﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using shop.entity;
using shop.util;
using System.Data.SqlClient;
using System.Data;
namespace shop.dao
{
    public class GoodsDao
    {
        public Goods GetGoods(int id)
        {
            string sql = "select g.id as goodsId,g.name,g.price,g.description,  c.Catagory_name as catagoryName, i.name as imageName,i.imagePath,i.showStatus,catatoryId from goods g inner join image i on g.imageId = i.id  inner join catagory c on g.id = @id";

            SqlParameter[] pms = {
                                 new SqlParameter("@id", id),

                                 };
            DataTable dt = SqlHelper.ExecuteDataTable(sql, pms);

            Goods goods = new Goods();
            if (dt.Rows.Count>0) {
                EntityAdapt(goods, dt.Rows[0]);
            }
           
            return goods;
        }
        public List<Goods> GetGoodsByCategoryId(int categoryId,int count)
        {
            string sql = "select top " + count + " g.id as goodsId, g.name,g.price, g.description, c.Catagory_name as catagoryName, i.name as imageName,i.imagePath,i.showStatus,catatoryId from goods g inner join image i on g.imageId = i.id inner join catagory c on g.catatoryId = c.id and catatoryId = @catatoryId order by g.id desc";

            SqlParameter[] pms = {
                                 new SqlParameter("@catatoryId", categoryId),
                                
                                 };
            DataTable dt = SqlHelper.ExecuteDataTable(sql, pms);

            List<Goods> goodsList = new List<Goods>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Goods goods = new Goods();
                EntityAdapt(goods, dt.Rows[i]);
                goodsList.Add(goods);
            }

            return goodsList;
        }
        public List<Goods> GetCarosels()
        {
            string sql = "select top 6 g.id as goodsId, g.name,g.price, g.description, i.name as imageName,i.imagePath,i.showStatus,catatoryId from goods g inner join image i on g.imageId = i.id  and catatoryId = 6 order by g.id desc";

           
            DataTable dt = SqlHelper.ExecuteDataTable(sql,null);

            List<Goods> goodsList = new List<Goods>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Goods goods = new Goods();
                goods.Id = (int)dt.Rows[i]["goodsId"];
                goods.Name = (string)dt.Rows[i]["name"];
                goods.Description = (string)dt.Rows[i]["description"];
                goods.Price = (double)dt.Rows[i]["price"];
                Image image = new Image();
                image.ImagePath = (string)dt.Rows[i]["imagePath"];
                //image.showStatus = (int)row["showStatus"];
                image.Id = (int)dt.Rows[i]["goodsId"];
                goods.Image = image;
                goodsList.Add(goods);
            }

            return goodsList;
        }
        public List<Goods> ListGoods(int count,Goods goodsSearch)
        {
            string sql = "select top  "+count + "g.id as goodsId,catatoryId ,g.name,g.price,g.description,c.Catagory_name as catagoryName,i.name as imageName,i.imagePath,i.showStatus from goods g inner join image i on g.imageId = i.id inner join [catagory] c on c.id=g.catatoryId";
            List<Goods> goodsList = new List<Goods>();
            SqlParameter[] pms = null;
            if (goodsSearch == null) {
                pms = null;
            } else if (goodsSearch.CategoryId>0) {
                sql += " and catatoryId = @categoryId ";
                pms = new SqlParameter[1];
               pms[0] = new SqlParameter("@categoryId", goodsSearch.CategoryId);                        
            }
            sql += " order by g.id desc";
            DataTable dt = SqlHelper.ExecuteDataTable(sql, pms);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Goods goods = new Goods();
                EntityAdapt(goods, dt.Rows[i]);
                goodsList.Add(goods);
            }

            return goodsList;
        }

        private void EntityAdapt(Goods goods, DataRow row)
        {
            goods.Id = (int)row["goodsId"];
            goods.Name = (string)row["name"];
            goods.Description = (string)row["description"];
            goods.Price = (double)row["price"];
            Image image = new Image();
            image.ImagePath = (string)row["imagePath"];
            //image.showStatus = (int)row["showStatus"];
            image.Id = (int)row["goodsId"];
            goods.Image = image;
            Catagory catagory = new Catagory() {
                Name = (string)row["catagoryName"]
        };
            goods.Catagory = catagory;
            goods.CategoryId = (int)row["catatoryId"];
            //Goods.ImageId = (int)row["imageId"];

        }
      
        public bool AddGoods(Goods goods)
        {
            string sql = "insert into goods(name,description,price,imageId,catatoryId)  values(@name,@description,@price,@imageId,@catatoryId)";
            Image image = new Image();
            image = goods.Image;
            SqlParameter[] pms = {
                                 new SqlParameter("@name", goods.Name),
                                 new SqlParameter("@description", goods.Description),
                                 new SqlParameter("@price", goods.Price),                               
                                   new SqlParameter("@imageId", image.Id),
                                   new SqlParameter("@catatoryId", goods.CategoryId)
                                 };
            int count = SqlHelper.ExecuteNonQuery(sql, pms);
            if (count > 0)
            {
                return true;
            }
            return false;
        }

        public bool UpdateGoods(Goods goods) 
        {
            string sql = "update goods set name = @name,description = @description ,price = @price,imageId = @imageId where id = @id";
            SqlParameter[] pms = { new SqlParameter("@id", goods.Id),
                                 new SqlParameter("@description", goods.Description),
                                 new SqlParameter("@price", goods.Price),
                                  new SqlParameter("@name", goods.Name),
                                    new SqlParameter("@imageId", 1)
                                 };
            
            int count = SqlHelper.ExecuteNonQuery(sql, pms);
            if (count > 0)
            {
                return true;
            }
            return false;
        }

        public bool DeleteGoods(int id)
        {
            string sql = "delete from goods where id = @id";
            SqlParameter[] pms = { new SqlParameter("@id", id) };
            int count = 0;          
            count = SqlHelper.ExecuteNonQuery(sql, pms);
            if (count > 0)
            {
                return true;
            }
            return false;
        }
    }
}